//
//  SqliteHelper.m
//  Palabris
//
//  Created by vedat on 28.12.12.
//  Copyright (c) 2012 vedat. All rights reserved.
//

#import "SqliteHelper.h"


@implementation SqliteHelper

- (id)init
{
    self = [super init];
    if (self) {
        [self createOrOpenDatabase];
    }
    return self;
}

-(void) createOrOpenDatabase{
    NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    //dbPathString = [[[paths objectAtIndex:0] stringByAppendingPathComponent:@"words.db"] retain];
    dbPathString = [[[paths objectAtIndex:0] stringByAppendingPathComponent:@"words.db"] init];
    NSFileManager *fileManager=[NSFileManager defaultManager];
    char *db_error;
    if (![fileManager fileExistsAtPath:dbPathString]) {
        const char *dbPath=[dbPathString UTF8String];
        if (sqlite3_open(dbPath, &databaseIPP)==SQLITE_OK) {
            //const char *sql_stmt="CREATE TABLE if not exists woerterbuch (wort TEXT primary key)";
            const char *sql_stmt="CREATE TABLE if not exists woerterbuch (wort TEXT)";
            sqlite3_exec(databaseIPP,sql_stmt,NULL,NULL,&db_error);
            sqlite3_close(databaseIPP);
        }
    }
}


- (void)addTableValue:(NSString*)value {
    char *error;
    //[self createOrOpenDatabase];
    if (sqlite3_open([dbPathString UTF8String], &databaseIPP)==SQLITE_OK) {
        NSString *insertStmt=[NSString stringWithFormat:@"insert into woerterbuch(wort) values('%@')",value];
        const char *insert_stmt=[insertStmt UTF8String];
        if (sqlite3_exec(databaseIPP,insert_stmt,NULL,NULL,&error)==SQLITE_OK) {
            //NSLog(@"eklendi");
        }
        sqlite3_close(databaseIPP);
    }
}



- (void)printAllTable {
	sqlite3_stmt *statement;
    int counter=0;
    [self createOrOpenDatabase];
    if (sqlite3_open([dbPathString UTF8String], &databaseIPP)==SQLITE_OK) {
        NSString *querySql=[NSString stringWithFormat:@"select * from woerterbuch"];
        const char *query_sql=[querySql UTF8String];
        if(sqlite3_prepare(databaseIPP, query_sql, -1, &statement, NULL) == SQLITE_OK) {
            while(sqlite3_step(statement) == SQLITE_ROW) {
                NSString *entry = [NSString stringWithCString:(const char*)sqlite3_column_text(statement, 0) encoding:NSUTF8StringEncoding];  //
                counter++;
                NSLog(@"Eintrag %i : %@",counter,entry);
            }
        }
        sqlite3_finalize(statement);
        [self closeDatabase];
    }
}


- (NSMutableArray*) getTableAllEntry {
    NSMutableArray *resultFields=[[NSMutableArray alloc] init];
	sqlite3_stmt *statement;
    int counter=0;
    [self createOrOpenDatabase];
    if (sqlite3_open([dbPathString UTF8String], &databaseIPP)==SQLITE_OK) {
        NSString *querySql=[NSString stringWithFormat:@"select * from woerterbuch"];
        const char *query_sql=[querySql UTF8String];
        if(sqlite3_prepare(databaseIPP, query_sql, -1, &statement, NULL) == SQLITE_OK) {
            while(sqlite3_step(statement) == SQLITE_ROW) {
                NSString *entry = [NSString stringWithCString:(const char*)sqlite3_column_text(statement, 0) encoding:NSUTF8StringEncoding];  //
                counter++;
                //NSLog(@"Eintrag %i , %@",counter,entry);
                [resultFields addObject:entry];
            }
        }
        sqlite3_finalize(statement);
        [self closeDatabase];
    }
    // TODO ARC resultFields
    return resultFields;
}


- (NSMutableArray*) findKey:(NSString*)key {
    NSMutableArray *resultKeys=[[NSMutableArray alloc] init];
	sqlite3_stmt *statement;
    //NSString *searchfilter = [NSString stringWithFormat:@"%@",@"%ung%"];
    int counter=0;
    [self createOrOpenDatabase];
    if (sqlite3_open([dbPathString UTF8String], &databaseIPP)==SQLITE_OK) {
        NSString *querySql=[NSString stringWithFormat:@"select * from woerterbuch where lower(wort) like '%@%@%@'",@"%",key.lowercaseString,@"%"];
        const char *query_sql=[querySql UTF8String];
        if(sqlite3_prepare(databaseIPP, query_sql, -1, &statement, NULL) == SQLITE_OK) {
            while(sqlite3_step(statement) == SQLITE_ROW) {
                NSString *entry = [NSString stringWithCString:(const char*)sqlite3_column_text(statement, 0) encoding:NSUTF8StringEncoding];  //
                counter++;
                //NSLog(@"Eintrag %i , %@",counter,entry);
                [resultKeys addObject:entry];
            }
        }
        sqlite3_finalize(statement);
        [self closeDatabase];
    }
    return resultKeys;
}



- (void)printKey:(NSString*)key {
	sqlite3_stmt *statement;
    NSString *searchfilter = [NSString stringWithFormat:@"'%@%@%@'",@"%",key.lowercaseString,@"%"];
    int counter=0;
    [self createOrOpenDatabase];
    if (sqlite3_open([dbPathString UTF8String], &databaseIPP)==SQLITE_OK) {
        NSString *querySql=[NSString stringWithFormat:@"select * from woerterbuch where lower(wort) like %@",searchfilter];
        const char *query_sql=[querySql UTF8String];
        NSLog(@"SQL %s",query_sql);
        if(sqlite3_prepare(databaseIPP, query_sql, -1, &statement, NULL) == SQLITE_OK) {
            while(sqlite3_step(statement) == SQLITE_ROW) {
                NSString *entry = [NSString stringWithCString:(const char*)sqlite3_column_text(statement, 0) encoding:NSUTF8StringEncoding];  //
                counter++;
                NSLog(@"Eintrag %i , %@",counter,entry);
            }
        }
        sqlite3_finalize(statement);
        [self closeDatabase];
    }
    NSLog(@"Eintrag %i ",counter);
}


- (int)getRowCount{
    int count=0;
    int column = 0;
    char*    errorMsg = NULL;
    char**    dbResult = NULL;
    [self createOrOpenDatabase];
    if (sqlite3_open([dbPathString UTF8String], &databaseIPP)==SQLITE_OK) {
        NSString *querySql=@"select * from woerterbuch";
        const char *query_sql=[querySql UTF8String];
        sqlite3_get_table(databaseIPP,query_sql, &dbResult, &count,&column,&errorMsg);
        if (errorMsg!=NULL) {
            NSLog(@"%s",errorMsg);
        }
        [self closeDatabase];
    }
    return count;
}


- (int)getKeyCount:(NSString*)key {
    NSString *searchfilter = [NSString stringWithFormat:@"'%@%@%@'",@"%",key.lowercaseString,@"%"];
    int count=0;
    int column = 0;
    char*    errorMsg = NULL;
    char**    dbResult = NULL;
    [self createOrOpenDatabase];
    if (sqlite3_open([dbPathString UTF8String], &databaseIPP)==SQLITE_OK) {
        NSString *querySql=[NSString stringWithFormat:@"select * from woerterbuch where lower(wort) like %@",searchfilter];
        const char *query_sql=[querySql UTF8String];
        sqlite3_get_table(databaseIPP,query_sql, &dbResult, &count,&column,&errorMsg);
        if (errorMsg!=NULL) {
            NSLog(@"%s",errorMsg);
        }
        [self closeDatabase];
    }
    return count;
}

-(void) removeAll{
    char *error;
    [self createOrOpenDatabase];
    if (sqlite3_open([dbPathString UTF8String], &databaseIPP)==SQLITE_OK) {
        NSString *querySql=[[NSString alloc] initWithFormat:@"delete from  woerterbuch"];
        //NSString *querySql=[NSString stringWithFormat:@"delete from  woerterbuch"];
        const char *query_sql=[querySql UTF8String];
        if(sqlite3_exec(databaseIPP, query_sql,NULL,NULL, &error) != SQLITE_OK) {
            NSLog(@"%s",error);
        }
        [self closeDatabase];
        //[querySql release];
    }
}

- (BOOL)isKeyContainer:(NSString*)key {
    NSString *searchfilter = [NSString stringWithFormat:@"'%@'",key.lowercaseString];
    int count=0;
    int column = 0;
    char*    errorMsg = NULL;
    char**    dbResult = NULL;
    [self createOrOpenDatabase];
    if (sqlite3_open([dbPathString UTF8String], &databaseIPP)==SQLITE_OK) {
        NSString *querySql=[NSString stringWithFormat:@"select * from woerterbuch where lower(wort)=%@",searchfilter];
        const char *query_sql=[querySql UTF8String];
        sqlite3_get_table(databaseIPP,query_sql, &dbResult, &count,&column,&errorMsg);
        if (errorMsg!=NULL) {
            NSLog(@"%s",errorMsg);
        }
        [self closeDatabase];
    }
    return count >0?YES:NO;
}

- (void)removeAllDataWithLength:(int)value {
    char *error;
    [self createOrOpenDatabase];
    if (sqlite3_open([dbPathString UTF8String], &databaseIPP)==SQLITE_OK) {
        NSString *querySql=[NSString stringWithFormat:@"delete  from woerterbuch where length(wort)<=%i",value];
        //NSString *querySql=[NSString stringWithFormat:@"delete from  woerterbuch"];
        const char *query_sql=[querySql UTF8String];
        NSLog(@"SQL %s",query_sql);
        if(sqlite3_exec(databaseIPP, query_sql,NULL,NULL, &error) != SQLITE_OK) {
            NSLog(@"%s",error);
        }
        [self closeDatabase];
        //[querySql release];
    }
}



- (void)closeDatabase {
	sqlite3_close(databaseIPP);
}


- (void) readWords:(NSString*) fileName  fileType:(NSString*)fileType {
    NSString * filename = [[NSBundle mainBundle] pathForResource:fileName ofType:fileType];
    NSError *error = nil;
    NSString *filecontent =
    [[NSString alloc] initWithContentsOfFile:filename
                                    encoding:NSUTF8StringEncoding
                                       error:&error];
    if(error!=nil) {
        NSLog(@"Cannot read file: %@",filename);
    }
    //Parse filecontent
    NSArray *lines = [filecontent componentsSeparatedByString:@"\n"];
    //[filecontent release];
    int counter=0;
    for(id names in lines) {
        counter++;
        //[self addTableValue:names];
        NSLog(@"%@", names);
    }
    NSLog(@"Zeilenanzahl%i", counter);
}


- (void) readWordsAndInsertTable:(NSString*) fileName  fileType:(NSString*)fileType {
    NSString * filename = [[NSBundle mainBundle] pathForResource:fileName ofType:fileType];
    NSError *error = nil;
    NSString *filecontent =
    [[NSString alloc] initWithContentsOfFile:filename
                                    encoding:NSUTF8StringEncoding
                                       error:&error];
    if(error!=nil) {
        NSLog(@"Cannot read file: %@",filename);
    }
    //Parse filecontent
    NSArray *lines = [filecontent componentsSeparatedByString:@"\n"];
    //[filecontent release];
    int counter=0;
    for(id names in lines) {
        counter++;
        
        NSRange range = [names rangeOfString:@"/"];
        if(range.location != NSNotFound){
            //NSLog(@"%@",[names substringWithRange:NSMakeRange(0,range.location)]);
            [self addTableValue:[names substringWithRange:NSMakeRange(0,range.location)]];
            
        }
        else{
            [self addTableValue:names];
        }
        if (counter % 1000 ==0) {
            NSLog(@"%i records are added in table",counter);
        }
    }
    NSLog(@"Zeilenanzahl%i", counter);
}




- (void) readWordsAndInsertTable3:(NSString*) fileName  fileType:(NSString*)fileType {
    NSString * filename = [[NSBundle mainBundle] pathForResource:fileName ofType:fileType];
    NSError *error = nil;
    NSString *filecontent =
    [[NSString alloc] initWithContentsOfFile:filename
                                    encoding:NSUTF8StringEncoding
                                       error:&error];
    if(error!=nil) {
        NSLog(@"Cannot read file: %@",filename);
    }
    
    
    
    
    
    
    //Parse filecontent
    NSArray *lines = [filecontent componentsSeparatedByString:@"\n"];
    
    
    
    
    
    //[filecontent release];
    if(sqlite3_open([dbPathString UTF8String],&databaseIPP)==SQLITE_OK)
    {
        sqlite3_exec(databaseIPP, "BEGIN", 0, 0, 0);
        const char *sqlstatement="insert into woerterbuch values(?)";
        sqlite3_stmt *compiledstatement;
        
        if(sqlite3_prepare_v2(databaseIPP,sqlstatement , -1, &compiledstatement, NULL)==SQLITE_OK)
        {
            int hasError= 0;
            int counter=0;
            for(id names in lines)
            {
                //if([names length]>1){// begin if
                NSRange range = [names rangeOfString:@"/"];
                NSString *inputValue;
                if(range.location != NSNotFound){
                    inputValue=[names substringWithRange:NSMakeRange(0,range.location)];
                    sqlite3_bind_text(compiledstatement,1,[inputValue UTF8String] , -1, SQLITE_TRANSIENT);
                }
                else{
                    inputValue=names;
                    sqlite3_bind_text(compiledstatement,1,[inputValue UTF8String], -1, SQLITE_TRANSIENT);
                }
                
                if(sqlite3_step(compiledstatement)==SQLITE_DONE)
                {
                    counter++;
                    //NSLog(@"%i---kayit yapildi %@",counter,names);
                }
                else {
                    NSLog(@"ERROR");
                    hasError= 1;
                }
                if (counter % 1000 ==0) {
                    NSLog(@"%i records are added in table",counter);
                }
                sqlite3_reset(compiledstatement);
            }
            // Really need to check error conditions with commit/rollback
            if( hasError == 0 ) {
                sqlite3_exec(databaseIPP, "COMMIT", 0, 0, 0);
            }
            else {
                sqlite3_exec(databaseIPP, "ROLLBACK", 0, 0, 0);
            }
            // }//ende if
        }
        sqlite3_close(databaseIPP);
    }
    else {
        NSLog(@"error");
    }
    
}

@end;